/**
 * Created by yunpeng on 2014/12/5.
 */

/**
 *MySQL Model
 **/
//var uuid = require('node-uuid'); uuid.v1() / uuid.v4()

var mq = require("mysql");
var DataGrid = require('../models/DataGrid');
var debug = require('debug')('common:query');
var config=require("../dbConfig.js");
var pool  = mq.createPool({
    connectionLimit : config.connectionLimit,
    host : config.host,
    port : config.port,
    database : config.database,
    user : config.user,
    password : config.password
});

var Table = function(tablename){
    this.tablename=tablename;
    this.pool = pool;
    //this.fields=fields;
}

Table.prototype.getConnection = function(callback) {
    if(!callback){
        callback=function(){};
    }
    this.pool.getConnection(function(err, connection) {
        if (err) {
            throw err;
        }
        callback(connection);
    });
};

//save
Table.prototype.insert = function(values, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    /*if (this.clearTable(values)) {*/
        /*if(typeof values["id_"]=='underfined'||values["id_"]==null || values["id_"]===""){
            values["id_"]=uuid.v1();
        }*/
        this.getConnection(function(connection) {
            var query = connection.query("insert into " + me.tablename + " set ?", values, function(err, result) {
                if (err) {console.log(err);
                    callback(err,null);
                }else{
                    callback(null,result);
                }
                connection.release(); //release
            });

            debug('add lifeService' + query.sql);
        })
    /*}*/
};

//get
Table.prototype.get = function(ID, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    if (ID != null && ID != "") {
        this.getConnection(function(connection) {
            var query = connection.query("select * from " + me.tablename + " where id_=?", ID, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); //release
            });
            debug(query.sql);
        });
    }
};

//update
Table.prototype.update = function(idName,values, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    /*if (this.clearTable(values)) {*/
        this.getConnection(function(connection) {
            var query = connection.query("update  " + me.tablename + " set ? where "+idName+"=" + connection.escape(values[idName]), values, function(err, result) {
                console.log(err);
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); //release
            });
            debug(query.sql);
        });
    /*}*/
};

Table.prototype.updateColumn = function(idName,idVal,values, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    /*if (this.clearTable(values)) {*/
    this.getConnection(function(connection) {
        var query = connection.query("update  " + me.tablename + " set ? where "+idName+"=" + connection.escape(idVal), values, function(err, result) {
            console.log(err);
            if (err) {
                callback(err,result);
            }else{
                callback(null,result);
            }
            connection.release(); //release
        });
        debug(query.sql);
    });
    /*}*/
};

//delete
Table.prototype.delete = function(idName, ids, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    if (ids != null && ids != "") {
        this.getConnection(function(connection) {
            var query = connection.query("delete from " + me.tablename + " where "+idName+" in ("+ids+")" , function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); //release
            });
            debug(query.sql);
        });
    }
}

//exists
Table.prototype.exists = function(tablename, callback) {
    if(!callback){
        callback=function(){};
    }
    if (tablename) {
        this.getConnection(function(connection) {
            var sql = "select table_name from information_schema.tables where table_schema='" + config_.database + "' and table_name='" + tablename + "'";
            var query = connection.query(sql, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); //release
            });
            debug(query.sql);
        });
    }
}

//clear
Table.prototype.clear = function(tablename, callback) {
    if(!callback){
        callback=function(){};
    }
    if (tablename) {
        this.getConnection(function(connection) {
            var sql = "TRUNCATE TABLE " + tablename;
            var query = connection.query(sql, function(err, result) {
                if (err) {
                    callback(err,result);
                }else{
                    callback(null,result);
                }
                connection.release(); //release
            });
            debug(query.sql);
        });
    }
}

//count
Table.prototype.count = function(condition1,callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    this.getConnection(function(connection) {
        var query = connection.query("select count(*) as count from " + me.tablename + ' where 1=1 '+condition1, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result[0].count);
            }
            connection.release(); //release
        });
        debug(query.sql);
    });
}
Table.prototype.countBySql = function(sql, p,callback) {
    if((typeof p == 'function') && p.constructor == Function){
        callback=p;
    }else{
        if (p) {
            for (var i=0;i<p.length;i++) {
                sql=sql.replace("?",this.pool.escape(p[i]));
            }
        }
    }
    if(!callback){
        callback=function(){};
    }
    this.getConnection(function(connection) {
        var query = connection.query("select count(*) as count from ( " + sql + " ) T", function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result[0].count);
            }
            connection.release(); //release
        });
        debug(query.sql);
    });
}

//query
Table.prototype.where = function(params, callback) {
    if(!callback){
        callback=function(){};
    }
    var sql = "select * from " + this.tablename + " where 1=1";
    if (this.clearTable(params)) {
        for (var pro in params) {
            sql += " and " + pro + "=" + this.pool.escape(params[pro]);
        }
    }

    this.getConnection(function(connection) {
        var query = connection.query(sql, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result);
            }
            connection.release(); //release
        });
        debug(query.sql);
    });
}
Table.prototype.where = function(params,orders, callback) {
    if(!callback){
        callback=function(){};
    }
    var sql = "select * from " + this.tablename + " where 1=1";
    if (this.clearTable(params)) {//参数
        for (var pro in params) {
            sql += " and " + pro + "=" + this.pool.escape(params[pro]);
        }
    }
    if(orders){//排序
        for (var pro in orders) {
            sql+=" order by "+pro+" " + orders[pro];
        }
    }

    if((typeof orders == 'function') && orders.constructor == Function){
        callback=orders;
    }

    this.getConnection(function(connection) {
        var query = connection.query(sql, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result);
            }
            connection.release(); //release
        });
        debug(query.sql);
    });
}
Table.prototype.queryAll = function(callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    this.getConnection(function(connection) {
        var query = connection.query("select * from " + me.tablename, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result);
            }
            connection.release(); //release
        });
        debug(query.sql);
    });
}
Table.prototype.queryPage = function(columns,condition1,condition2, callback) {
    if(!callback){
        callback=function(){};
    }
    var me=this;
    this.count(condition1,function(err,totalCount) {
        if(err){
            callback(err,null);
        }else{
            //计数
            //page.totalCount = totalCount;
            //page.totalPage = Math.ceil(page.totalCount / page.pageSize);
            //分页
            me.getConnection(function(connection) {
                var query = connection.query("select "+columns+" from " + me.tablename + " where 1=1"+condition1+" ORDER BY "+connection.escapeId("l." +condition2[0])+ " "+condition2[1] +"  LIMIT "+condition2[2]+" , "+condition2[3], function(err, rows) {
                    if (err) {
                        callback(err,null);
                    }else{
                        callback(null,new DataGrid(totalCount,rows));
                    }
                    connection.release(); //release

                });
                debug(query.sql);
            });
        }
    });
}

Table.prototype.queryPageBySql = function(sql, page,params, callback) {
    if(!callback){
        callback=function(){};
    }
    if(params && (typeof params == 'function') && params.constructor == Function){
        callback=params;
    }

    if ((params)) {
        for (var i=0;i<params.length;i++) {
            sql=sql.replace("?",this.pool.escape(params[i]));
        }
    }
    var me=this;
    this.countBySql(sql, function(err,result) {
        if(err){
            callback(err,result);
        }else{
            //计数
            page.totalCount = result;
            page.totalPage = Math.ceil(page.totalCount / page.pageSize);
            //分页
            me.getConnection(function(connection) {
                var query = connection.query("select * from  ( " + sql + " ) T limit " + page.start + "," + page.end + "", function(err, result) {
                    if (err) {
                        callback(err,result);
                    }else{
                        page.data = result;
                        callback(null,page);
                    }
                    connection.release(); //release
                });
                debug(query.sql);
            });
        }
    });
}

Table.prototype.queryPage1 = function(page,params, callback) {
    if(!callback){
        callback=function(){};
    }
    var sql = "select * from " + this.tablename + " where 1=1";
    if (this.clearTable(params)) {
        for (var pro in params) {
            sql += " and " + pro + "=" + this.pool.escape(params[pro]);
        }
    }
    this.queryPageBySql(sql,page,callback);
}


Table.prototype.executeSql = function(sql,params, callback) {
    if(!callback){
        callback=function(){};
    }
    if ((params)) {
        for (var i=0;i<params.length;i++) {
            sql=sql.replace("?",this.pool.escape(params[i])) ;
        }
    }
    this.getConnection(function(connection) {
        var query = connection.query(sql, function(err, result) {
            if (err) {
                callback(err,result);
            }else{
                callback(null,result);
            }
            connection.release(); //release
        });
        debug(query.sql);
    });
}

module.exports.Table = Table;

/*var createPool = function() {
    if(pool==null){
        pool=mq.createPool({
            connectionLimit : config.connectionLimit,
            host : config.host,
            port : config.port,
            database : config.database,
            user : config.user,
            password : config.password
        });
    }
    return pool;
}*/

//查询工具类
/*function DBUtil() {
    this.pool = createPool();
    this.tables = [];
};*/

/*DBUtil.prototype.define = function(table) {
    var me=this;
    if(util.isArray(table)){
        table.forEach(function(r){
            me.tables.push([r.key,new Table(r.name,me,r.fields)]);
        });
    }else{
        me.tables.push([table.key,new Table(table.name,me,table.fields)]);
    }
};
DBUtil.prototype.get=function(tablename){
    if(tablename){
        var _len=this.tables.length;
        for(i=0;i<_len;i++){
            if(this.tables[i][0]==tablename){
                return  this.tables[i][1];
            }
        }
    }
    return null;
};*/

/*exports.Instance = function() {
    if (db == null) {
        db = new DBUtil();
    }
    return db;
};*/


//test
/*var db = this.Instance();
 var User = db.define('t_ef_user');
 User.get('1', function(result) {
 console.log(result);
 });*/


